﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace Dashboard.ProductBonus.Business
{
    public class WorkingShift : BusinessBase
    {
        public DataTable GetValidWorkingShiftList()
        {
            string sql = "select id,Code,Description,convert(varchar,FromTime,120) AS FromTime,Convert(varchar,ToTime,120) AS ToTime from workingshift where isvalid='Y' ORDER BY code";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public bool IsWorkingShiftUsed(int id)
        {
            string sql = "select count(*) from WorkerShiftSchedule where workingshift=" + id + " and workingdate>=getdate()";
            int result = Convert.ToInt32(DbConnection.GetValueOfField(sql));

            if (result > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public void DeleteWorkingShiftById(int id)
        {
            string sql = "update workingshift set isvalid='N',updatedby='"
                + CommonBusiness.GetCurrentLoginUser() + "',updateddate=getdate() WHERE id=" + id;
            DbConnection.ExecuteSql(sql);
        }

        public DataTable GetWorkingShiftById(int id)
        {
            string sql = "select id,code,description,convert(varchar,FromTime,120) AS FromTime,Convert(varchar,ToTime,120) AS ToTime from workingshift where id=" + id;
            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GetWorkingShift(int id, string code, DateTime fromTime, DateTime toTime, bool isValid)
        {
            string sql = "select * from workingshift where id<>" + id + " and code='" + code + "' and fromtime='" + fromTime.ToString("HH:mm:ss") + "' and totime='" + toTime.ToString("HH:mm:ss") + "'";

            if (isValid)
            {
                sql += " and isvalid = '" + (isValid ? "Y" : "N") + "'";
            }

            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GetWorkingShift(int id, string code, DateTime fromTime, DateTime toTime)
        {
            string sql = "select * from workingshift where id<>" + id + " and code='" + code + "' and fromtime='" + fromTime.ToString("HH:mm:ss") + "' and totime='" + toTime.ToString("HH:mm:ss") + "'";

            return DbConnection.DataSet(sql).Tables[0];
        }

        public void AddNewWorkingShift(string code, string description, DateTime fromTime, DateTime toTime, bool isValid)
        {
            int totalTime = this.CalculateTotalTime(fromTime, toTime);

            string sql = "insert into workingshift(code,description,fromtime,totime,totaltime,isvalid,insertby,insertdate,updatedby,updateddate) ";
            sql += " VALUES('" + code + "', '" + description + "', '" + fromTime.ToString("HH:mm") + "','" + toTime.ToString("HH:mm") + "'," + totalTime + ",'" + (isValid ? "Y" : "N") + "','" + CommonBusiness.GetCurrentLoginUser() + "',getdate(),'" + CommonBusiness.GetCurrentLoginUser() + "',getdate())";

            DbConnection.ExecuteSql(sql);
        }

        public void UpdateWorkingShift(int id, string description, DateTime fromTime, DateTime toTime, bool isValid)
        {
            int totalTime = this.CalculateTotalTime(fromTime, toTime);

            string sql = "update workingshift set description='" + base.FormatStringForSqlVariable(description) + "', fromtime = '"
                + fromTime.ToString("HH:mm") + "', totime='" + toTime.ToString("HH:mm") + "', totaltime=" + totalTime + ", isvalid='" + (isValid ? "Y" : "N") + "',updatedby='"
                + CommonBusiness.GetCurrentLoginUser() + "',updateddate=getdate() WHERE id=" + id;

            DbConnection.ExecuteSql(sql);
        }

        public bool IsWorkingShiftExists(string code)
        {
            string sql = "SELECT * FROM workingshift where code='" + code + "' AND isvalid='Y'";
            DataTable dt = DbConnection.DataSet(sql).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public int CalculateTotalTime(DateTime fromTime, DateTime toTime)
        {
            if (toTime < fromTime)
            {
                if (toTime.Date == fromTime.Date)
                {
                    toTime = toTime.AddDays(1);
                }
                else
                {
                    toTime = new DateTime(fromTime.Year, fromTime.Month, fromTime.Day, toTime.Hour, toTime.Minute, toTime.Second);
                    toTime = toTime.AddDays(1);
                }
            }

            int totalTime = toTime.Subtract(fromTime).Hours % 24;

            return totalTime;
        }
    }
}
